﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SQLite;
using Com.Toook.Common;
using System.Text.RegularExpressions;

public partial class nst_joke_list : BasePage
{
    protected string tableName = "UncheckedJoke";
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
        {
            InitMovie();
        }
    }
    private void InitMovie()
    {
        tableName = txtDataTableName.Text.Trim();
        string sql = "select id, title, siteKindId, content " +
            " from " + tableName +
            " where " + txtSiteKindId.Text.Trim() + " AND edited=0 limit 0, 10";//SQLiteDatabase
        SQLiteConnection con = SqliteHelper.GetConnection(txtDatabaseName.Text.Trim());
        SQLiteCommand cmd = new SQLiteCommand(sql, con);
        SQLiteDataReader reader = null;
        try
        {
            con.Open();
            reader = cmd.ExecuteReader();
            GridViewJoke.DataSource = reader;
            GridViewJoke.DataBind();
        }
        catch(Exception ex)
        {
            LogHelper.Log.Error(ex);
        }
        finally
        {
            SqliteHelper.CloseDbResource(con, reader);
        }
    }
    protected void ButtonRefreshData_Click(object sender, EventArgs e)
    {
        InitMovie();
    }
    protected void ButtonPublish_Click(object sender, EventArgs e)
    {
        foreach(GridViewRow row in GridViewJoke.Rows)
        {
            CheckBox checkId = (CheckBox)row.FindControl("CheckBoxId");
            if(checkId.Checked)
            {
                //ids += row.Cells[1].Text + ",";
                TransferToPublish(row.Cells[1].Text, false);
            }
        }
        InitMovie();
    }
    protected void ButtonAllPublish_Click(object sender, EventArgs e)
    {
        while(true)
        {
            string sql = "select id from " + txtDataTableName.Text.Trim() +
                " where " + txtSiteKindId.Text.Trim() + " and edited=0 limit 0,1 ";
            SQLiteConnection con = SqliteHelper.GetConnection(txtDatabaseName.Text.Trim());
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            SQLiteDataReader reader = null;
            object id = null;
            try
            {
                con.Open();
                id = cmd.ExecuteScalar();
            }
            catch(Exception ex)
            {
                LogHelper.Log.Error(ex);
                return;
            }
            finally
            {
                SqliteHelper.CloseDbResource(con, reader);
            }
            if(id == null || id.ToString().Length < 1)
            {
                lblMsg.Text = "完成。id==null = " + (id == null).ToString();
                break;
            }
            else
            {
                TransferToPublish(id.ToString(), true);
            }
        }
    }
    private void TransferToPublish(string id, bool isAll)
    {
        int type;
        if(isAll)
        {
            type = int.Parse(ddlAll.SelectedValue);
        }
        else
        {
            type = int.Parse(ddlJokeType.SelectedValue);
        }
        //1、查询数据
        string sql = "select id, title, content" +
            " from " + txtDataTableName.Text.Trim() + " where id=" + id;//SQLiteDatabase
        SQLiteConnection con = SqliteHelper.GetConnection(txtDatabaseName.Text.Trim());
        SQLiteCommand cmd = new SQLiteCommand(sql, con);
        SQLiteDataReader reader = null;

        string title = "";        
        string content = "";
        
        try
        {
            con.Open();
            reader = cmd.ExecuteReader();
            if(reader.Read())
            {
                title = reader["title"].ToString();                
                content = reader["content"].ToString();
                content = content.Replace("&nbsp;", "").Replace("&quot;", "\"").Replace("&middot;", "·").Replace("</p> <p>", "</p><p>").Replace("</font>", "").Replace("</span", "").Replace("<font face=\"Verdana\">", "");
                content = Regex.Replace(content, @"<font\b([^>]+)>", "", RegexOptions.IgnoreCase);
                content = Regex.Replace(content, @"<span\b([^>]+)>", "", RegexOptions.IgnoreCase);
                content = Regex.Replace(content, @"<a\b([^>]+)>(.*?)</a>", "", RegexOptions.IgnoreCase);
            }
        }
        catch(Exception ex)
        {
            LogHelper.Log.Error(ex);
            return;
        }
        finally
        {
            SqliteHelper.CloseDbResource(con, reader);
        }
        
        //2、插入到正式表
        string sqlInsert = "insert into JokePublish(title, type, content) " +
            " values(@title, @type, @content)";
        SQLiteConnection conInsert = SqliteHelper.GetConnection("SQLiteDatabase");
        SQLiteCommand cmdInsert = new SQLiteCommand(sqlInsert, conInsert);
        cmdInsert.Parameters.Add("@title", DbType.String, 30).Value = title;        
        cmdInsert.Parameters.Add("@content", DbType.String).Value = content;       
        cmdInsert.Parameters.Add("@type", DbType.Int32).Value = type;
        bool transferOK = true;
        try
        {
            conInsert.Open();
            cmdInsert.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
            transferOK = false;
            LogHelper.Log.Error(ex);
        }
        finally
        {
            SqliteHelper.CloseDbResource(conInsert, reader);
        }

        //3、更新临时表状态
        if(transferOK)
        {
            string sqlUpdate = "update " + txtDataTableName.Text.Trim() + " set edited=1 where id=" + id;
            SQLiteCommand cmdUpdate = new SQLiteCommand(sqlUpdate, con);

            try
            {
                con.Open();
                cmdUpdate.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                LogHelper.Log.Error(ex);
            }
            finally
            {
                SqliteHelper.CloseDbResource(con, null);
            }
        }
    }
}
